(김준회) 나준규 프로 DB조회 우회 요청사항 (임시)

2 files changed, 206 insertions, 0 deletions

diff --git a/app/[lng]/admin/temp-db-viewer/actions.ts b/app/[lng]/admin/temp-db-viewer/actions.ts
new file mode 100644
index 00000000..753c1b28
--- /dev/null
+++ b/app/[lng]/admin/temp-db-viewer/actions.ts
@@ -0,0 +1,65 @@
+"use server"
+
+/**
+ * 사적이고 우회적인 요청사항...
+
+-- readonly 사용자 생성 (비밀번호 설정)
+CREATE USER readonly WITH PASSWORD 'tempReadOnly_123';
+
+
+-- evcp 데이터베이스에 연결할 수 있는 권한 부여
+GRANT CONNECT ON DATABASE evcp TO readonly;
+
+-- 조회할 스키마 사용 권한 부여
+GRANT USAGE ON SCHEMA public TO readonly;
+GRANT USAGE ON SCHEMA soap TO readonly;
+GRANT USAGE ON SCHEMA nonsap TO readonly;
+GRANT USAGE ON SCHEMA mdg TO readonly;
+
+-- 기존 모든 테이블에 대한 SELECT 권한 부여
+GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
+GRANT SELECT ON ALL TABLES IN SCHEMA soap TO readonly;
+GRANT SELECT ON ALL TABLES IN SCHEMA nonsap TO readonly;
+GRANT SELECT ON ALL TABLES IN SCHEMA mdg TO readonly;
+
+ */
+
+import { Pool } from "pg"
+
+export interface QueryResultState {
+  columns: string[]
+  // eslint-disable-next-line @typescript-eslint/no-explicit-any
+  rows: Record<string, any>[]
+  error?: string
+}
+
+export async function executeSqlAction(
+  prevState: QueryResultState,
+  formData: FormData
+): Promise<QueryResultState> {
+  const query = (formData.get("query") as string | null) ?? ""
+
+  if (!query.trim()) {
+    return { ...prevState, error: "쿼리를 입력해주세요." }
+  }
+
+  try {
+    const connectionString = process.env.READONLY_DB_URL
+
+    if (!connectionString) {
+      return { ...prevState, error: "READONLY_DB_URL 환경변수가 설정되지 않았습니다." }
+    }
+
+    const pool = new Pool({ connectionString })
+    const result = await pool.query(query)
+    await pool.end()
+
+    return {
+      columns: result.fields.map((f) => f.name),
+      // eslint-disable-next-line @typescript-eslint/no-explicit-any
+      rows: result.rows as Record<string, any>[],
+    }
+  } catch (err) {
+    return { ...prevState, error: (err as Error).message }
+  }
+} 
\ No newline at end of file
diff --git a/app/[lng]/admin/temp-db-viewer/page.tsx b/app/[lng]/admin/temp-db-viewer/page.tsx
new file mode 100644
index 00000000..6692e63e
--- /dev/null
+++ b/app/[lng]/admin/temp-db-viewer/page.tsx
@@ -0,0 +1,141 @@
+"use client";
+
+import * as React from "react";
+import { useActionState, useState } from "react";
+import { executeSqlAction, type QueryResultState } from "./actions";
+import { Textarea } from "@/components/ui/textarea";
+import { Button } from "@/components/ui/button";
+import { toast } from "sonner";
+
+// CSV 변환 유틸
+function convertToCSV(columns: string[], rows: Record<string, any>[]): string {
+  const escape = (value: any) => {
+    if (value === null || value === undefined) return "";
+    const str = String(value).replace(/"/g, '""');
+    return `"${str}"`;
+  };
+
+  const header = columns.map(escape).join(",");
+  const lines = rows.map((row) =>
+    columns.map((col) => escape(row[col])).join(",")
+  );
+  return [header, ...lines].join("\r\n");
+}
+// ────────────────────────────────────────────────────────────────────────────────
+// Main page component
+// ────────────────────────────────────────────────────────────────────────────────
+
+export default function SqlEditorPage() {
+  const [query, setQuery] = useState<string>("");
+
+  const initialState: QueryResultState = {
+    columns: [],
+    rows: [],
+  };
+
+  // useActionState: 서버 액션과 클라이언트 상태 연결
+  const [state, formAction, isPending] = useActionState<
+    QueryResultState,
+    FormData
+  >(executeSqlAction, initialState);
+
+  // CSV 내보내기 핸들러
+  const handleExportCSV = React.useCallback(() => {
+    if (state.rows.length === 0) {
+      toast.info("내보낼 결과가 없습니다.");
+      return;
+    }
+
+    const csv = convertToCSV(state.columns, state.rows);
+    const blob = new Blob([csv], { type: "text/csv;charset=euc-kr;" });
+    const url = URL.createObjectURL(blob);
+    const link = document.createElement("a");
+    link.href = url;
+    link.download = "query_result.csv";
+    link.click();
+    URL.revokeObjectURL(url);
+  }, [state.columns, state.rows]);
+
+  // 오류 toast 표시
+  React.useEffect(() => {
+    if (state.error) {
+      toast.error(state.error);
+    }
+  }, [state.error]);
+
+  return (
+    <div className="w-full p-4 flex flex-col h-[100dvh] gap-4">
+      {/* 상단: 쿼리 입력 영역 */}
+      <form
+        action={formAction}
+        className="flex flex-col min-h-0 space-y-4 overflow-auto"
+      >
+        <Textarea
+          name="query"
+          className="flex font-mono text-sm"
+          value={query}
+          onChange={(e) => setQuery(e.target.value)}
+          placeholder="조회가능스키마: public, mdg, nonsap, soap(로그스키마)"
+          disabled={isPending}
+        />
+        <div className="flex justify-end gap-2">
+          <p className="text-sm text-muted-foreground">조회된 행 수: {state.rows.length}</p>
+          <div className="flex gap-2">
+            <Button type="submit" disabled={isPending}>
+              {isPending ? "실행 중..." : "실행"}
+            </Button>
+            <Button
+              type="button"
+              variant="outline"
+              onClick={handleExportCSV}
+              disabled={state.rows.length === 0}
+            >
+              CSV 내보내기
+            </Button>
+          </div>
+        </div>
+      </form>
+
+      {/* 하단: 결과 테이블 영역 */}
+      <div className="flex-1 overflow-auto p-4 border rounded-md">
+        {state.rows.length === 0 ? (
+          <p className="text-sm text-muted-foreground">
+            {isPending
+              ? "쿼리 실행 중"
+              : "결과 여기 표시됨"}
+          </p>
+        ) : (
+          <div className="w-full overflow-auto">
+            <table className="w-full border-collapse text-sm">
+              <thead>
+                <tr>
+                  {state.columns.map((col) => (
+                    <th
+                      key={col}
+                      className="border bg-muted px-2 py-1 text-left font-medium"
+                    >
+                      {col}
+                    </th>
+                  ))}
+                </tr>
+              </thead>
+              <tbody>
+                {state.rows.map((row, rowIdx) => (
+                  <tr key={rowIdx} className="odd:bg-muted/30">
+                    {state.columns.map((col) => (
+                      <td key={col} className="border px-2 py-1">
+                        {row[col] === null || row[col] === undefined
+                          ? "NULL"
+                          : String(row[col])}
+                      </td>
+                    ))}
+                  </tr>
+                ))}
+              </tbody>
+            </table>
+          </div>
+        )}
+      </div>
+    </div>
+  );
+}